#!/usr/bin/env python
import sys
import os
from PyQt4.QtCore import *
from PyQt4.QtGui import *
import pyExcelerator
from cvs.xls2csv import *
from Import.Import import *
import threading
import sqlite3 as dbapi
import GUI.MainWindow_qrc


class MainWindow(QMainWindow):
    def __init__(self):
        QMainWindow.__init__(self)
        self.setWindowTitle("EvapoGIS")
        self.mdi = QWorkspace()
        self.setCentralWidget(self.mdi)
        self.mdi.palette().setBrush(QPalette.Normal,QPalette.Window,QColor(255,255,255))
        self.progressWidget = QWidget()
        self.progressBar = QProgressBar()
        self.progressBar.setRange(0, 10000)
        self.progressBar.setValue(0)
        self.mdi.addWindow(self.progressBar)
        self.progressLabel = QLabel("Esto puede tardar unos minutos...")
        self.progresslayout = QVBoxLayout()
        self.progresslayout.addWidget(self.progressBar)
        self.progresslayout.addWidget(self.progressLabel)
        self.progressWidget.setLayout(self.progresslayout)
        self.mdi.addWindow(self.progressWidget)
        self.progressWidget.hide()
        self.itemComboBox = QComboBox()
        self.connect(self.itemComboBox,
                SIGNAL("currentIndexChanged(int)"), self.tableDBupdate)
        self.inicio()
        #self.sqlQueries()

        
        #self.setFocusPolicy(Qt.StrongFocus)
        #self.setAttribute(Qt.WA_QuitOnClose,True)
        #self.setBackgroundRole(QPalette.Base)
        #self.setAttribute(Qt.WA_NoSystemBackground)

        #Lista para ToolBar
        
        #logDockWidget = QDockWidget(self)
        #self.tablaDb = self.itemComboBox.currentText().split(".")[0]
        #self.consolaWidget = QLabel("<font color=white size=3>"+self.tablaDb+"</font>")
        #self.consolaWidget2 = QLabel("<font color=white size=3>Directorio: "+path+"</font>")
        #self.consolaWidget3 = QLabel("<font color=white size=3>Plataforma: "+os.name+"</font>")
        #consolaWidget = QWidget()
        #consolaLayout = QHBoxLayout()
        #consolaLayout.addWidget(self.consolaWidget)
        #consolaLayout.addWidget(self.consolaWidget2)
        #consolaLayout.addWidget(self.consolaWidget3)
        #consolaWidget.setLayout(consolaLayout)
        #consolaWidget.resize(self.size().width(),self.size().height()*0.01)
        #logDockWidget.resize(self.size().width(),self.size().height()*0.01)
        #logDockWidget.setWidget(consolaWidget)
        #elf.addDockWidget(Qt.BottomDockWidgetArea, logDockWidget)
        
        manualDockWidget = QDockWidget("Manual", self)
        self.textBrowser = QTextEdit()
        html = QFile(":/index.html")
        html = html.readData(10000)
        html = html.split("<html>")[1]
        self.textBrowser.setHtml(html)
        manualDockWidget.setWidget(self.textBrowser)
        self.addDockWidget(Qt.LeftDockWidgetArea, manualDockWidget)
        self.textBrowser.show()
        
        self.agrotimer = QTimer()
        self.connect(self.agrotimer,SIGNAL("timeout()"), self.mostrar_openagro)
        self.agrotimer.start(60000)
                


        #Splash Instancia
        self.scene = QGraphicsScene()
        self.grafica = QGraphicsView(self.scene)
        self.splitter2=QSplitter(Qt.Horizontal)
        self.splitter2.addWidget(self.grafica)
        self.splash = SplashItem()
        self.scene.addItem(self.splash)
        self.splash.grabKeyboard()     
        
    def mostrar_openagro(self):
        pictureDockWidget = QDockWidget("GPL Software ", self)
        self.pictureAgro = QPixmap()
        self.pictureAgro.load(":/images/OpenAgro.png")
        pictureLabel = QLabel()
        openagroLabel = QLabel(QString(
        'Openagro es el grupo de software libre y <br>' 
        'agricultura de america latina visite el site: <br>'
        'http://openagro.appspot.com'))
        pictureLabel.setPixmap(self.pictureAgro)
        self.openagroWidget = QWidget()
        agroLayout = QVBoxLayout()
        agroLayout.addWidget(pictureLabel)
        agroLayout.addWidget(openagroLabel)
        self.openagroWidget.setLayout(agroLayout)
        pictureDockWidget.setWidget(self.openagroWidget)
        self.addDockWidget(Qt.LeftDockWidgetArea, pictureDockWidget)
        self.openagroWidget.show()
    	self.agrotimer.stop()
    	
    	
        
    def closeEvent(self, event):
        if self.trayIcon.isVisible():
            QMessageBox.information(self, self.tr("EvapoGIS"),
                    self.tr("Este programa seguira ejecutandose en su sistema "
                        "Para terminar el programa seleccione <b>Quit</b>"
                        " dando click derecho en el icono de la barra de tareas."))
            self.hide()
            event.ignore()
        
    def contextMenuEvent(self, event):
        self.menu = QMenu(self.mdi)
        iniAction = self.menu.addAction("&Inicio")
        conAction = self.menu.addAction("&Consulta")
        self.menu.addSeparator()
        aboutAction = self.menu.addAction("&About")
        self.connect(iniAction, SIGNAL("triggered()"), self.inicio)
        self.connect(conAction, SIGNAL("triggered()"), self.runSQLquery)
        self.connect(aboutAction, SIGNAL("triggered()"), self.ataque)
        self.menu.exec_(event.globalPos())
        
    def createTrayIcon(self):
         self.trayIconMenu = QMenu(self)
         self.trayIconMenu.addAction(self.minimizeAction)
         self.trayIconMenu.addAction(self.maximizeAction)
         self.trayIconMenu.addAction(self.restoreAction)
         self.trayIconMenu.addSeparator()
         self.trayIconMenu.addAction(self.quitAction)

         self.trayIcon = QSystemTrayIcon(self)
         self.trayIcon.setContextMenu(self.trayIconMenu)
         self.trayIcon.setIcon(QIcon(":/images/tux.png"))
         
         self.connect(self.trayIcon,
                SIGNAL("activated(QSystemTrayIcon::ActivationReason)"),
                self.iconActivated)
    
    def iconActivated(self, reason):
        if reason == QSystemTrayIcon.MiddleClick:
            self.showMessage()
         
    def showMessage(self):
        icon = QSystemTrayIcon.MessageIcon(QSystemTrayIcon.Information)
        self.trayIcon.showMessage("Visite la web del proyecto:",
                "http://openagro.appspot.com, o escriba al correo dbcaturra@gmail.com", icon,
                30 * 1000)
                
    def showMessageNueva(self):
        icon = QSystemTrayIcon.MessageIcon(QSystemTrayIcon.Information)
        self.trayIcon.showMessage("Esta creando una nueva base de datos",
                "Escoja un directorio para crear la base de datos de tal manera de que en el exita la carpeta xls/ o cvs/con los informacion a importar", icon,
                30 * 1000)

    def inicio(self):
        items = QStringList()
        items << self.tr("Nueva DB") << self.tr("Abrir DB") 
        item, ok = QInputDialog.getItem(self, self.tr("Bienvenido a EvapoGIS"),
                                              self.tr("Seleccione:"), items, 0, False)
        if ok and not item.isEmpty():
            #self.itemLabel.setText(item)
            if item=="Nueva DB":
                self.dbfileName = QFileDialog.getSaveFileName(self,self.tr("Crear base de datos .db"),"/",self.tr("*"))
                self.itemComboBox.clear()

                




            if item=="Abrir DB":
                self.dbfileName = QFileDialog.getOpenFileName(self,
                                         self.tr("Abrir base de datos"),"/",
                                         self.tr("*"))
                self.itemComboBox.clear()
                print """borrado"""

            	self.updatelistXLS()				
                
                
    def updatelistXLS(self):
        path = os.path.dirname(str(self.dbfileName))
        if "xls" in os.listdir(path):
		    for xls in sorted(os.listdir(os.path.join(path, "xls"))):
		    	if xls.endswith(".xls"):
		    		self.itemComboBox.addItem(xls)


            
            
                
        else:
            if self.trayIcon:
                self.showMessageNueva()
            
			                           


    
    def tableDBupdate(self):
        self.tablaDb = self.itemComboBox.currentText()
        self.tablaDb = self.tablaDb.split(".")[0]
        #self.consolaWidget.setText(self.tablaDb)
        self.runSQLquery()
        self.editquery.setText(self.tablaDb)
    

    def createActions(self):
        self.newAct = QAction(QIcon(":/images/tux.png"), self.tr("&About"), self)
        self.newAct.setShortcut(self.tr("Ctrl+A"))
        self.newAct.setStatusTip(self.tr("About this program"))
        self.connect(self.newAct, SIGNAL("triggered()"), self.ataque)
        
        self.openAct = QAction(QIcon(":/images/open.png"), self.tr("&Importar cvs a db"), self)
        self.openAct.setShortcut(self.tr("Ctrl+D"))
        self.openAct.setStatusTip(self.tr("Importar un archivo cvs a una base de datos"))
        self.connect(self.openAct, SIGNAL("triggered()"), self.fileOpen)
        
        self.importcvsAct = QAction(QIcon(":/images/cvs.png"), self.tr("&Importar xls a cvs"), self)
        self.importcvsAct.setShortcut(self.tr("Ctrl+O"))
        self.importcvsAct.setStatusTip(self.tr("Importar xls to cvs"))
        self.connect(self.importcvsAct, SIGNAL("triggered()"), self.importcvsOpen)
        
        self.homeAct = QAction(QIcon(":/images/home.png"), self.tr("&Inicio"), self)
        self.homeAct.setShortcut(self.tr("Ctrl+I"))
        self.homeAct.setStatusTip(self.tr("Inicio"))
        self.connect(self.homeAct, SIGNAL("triggered()"), self.inicio)

        self.queryAct = QAction(QIcon(":/images/consulta.png"), self.tr("&Consulta"), self)
        self.queryAct.setShortcut(self.tr("Ctrl+B"))
        self.queryAct.setStatusTip(self.tr("Consulta Sql"))
        self.connect(self.queryAct, SIGNAL("triggered()"), self.runSQLquery)

        self.penmanAct = QAction(QIcon(":/images/new.png"), self.tr("&Penmanmontein"), self)
        self.penmanAct.setShortcut(self.tr("Ctrl+B"))
        self.penmanAct.setStatusTip(self.tr("PenmainMontein"))
        self.connect(self.penmanAct, SIGNAL("triggered()"), self.penmanMotein)
        
        #TryIcon
        self.minimizeAction = QAction(QIcon(":/images/2downarrow.png"),self.tr("Mi&nimize"), self)
        self.connect(self.minimizeAction,
                SIGNAL("triggered()"), SLOT("hide()"))

        self.maximizeAction = QAction(QIcon(":/images/2uparrow.png"),self.tr("Ma&ximize"), self)
        self.connect(self.maximizeAction,
                SIGNAL("triggered()"), SLOT("showMaximized()"))

        self.restoreAction = QAction(QIcon(":/images/back.png"),self.tr("&Restore"), self)
        self.connect(self.restoreAction,
                SIGNAL("triggered()"), SLOT("showNormal()"))

        self.quitAction = QAction(QIcon(":/images/stopScript.png"),self.tr("&Quit"), self)
        QObject.connect(self.quitAction, SIGNAL("triggered()"),
                qApp,SLOT("quit()"))
        
        
        #****
        
    def createMenus(self):
        self.fileMenu = self.menuBar().addMenu(self.tr("&File"))
        self.fileMenu.addAction(self.homeAct)
        self.fileMenu.addAction(self.openAct)
        self.fileMenu.addAction(self.importcvsAct)
        self.fileMenu.addSeparator()
        self.fileMenu.addAction(self.newAct)
        self.queryMenu = self.menuBar().addMenu(self.tr("&Consulta"))
        self.queryMenu.addAction(self.queryAct)
        self.queryMenu = self.menuBar().addMenu(self.tr("&Modelos"))
        self.queryMenu.addAction(self.penmanAct)



    def createToolBars(self):
        self.fileToolBar = self.addToolBar(self.tr("File"))
        self.fileToolBar.addAction(self.homeAct)
        self.fileToolBar.addAction(self.openAct)
        self.fileToolBar.addAction(self.importcvsAct)
        self.helpToolBar = self.addToolBar(self.tr("Help"))
        self.addToolBar (Qt.TopToolBarArea,self.helpToolBar)
        self.helpToolBar.addAction(self.queryAct)
        self.helpToolBar.addAction(self.newAct)
        self.helpToolBar.addWidget(self.itemComboBox)
        #self.helpToolBar.addWidget(self.sqlWidget)
        
        



    def createStatusBar(self):
        self.statusBar().showMessage(self.tr("Ready"))

    def readSettings(self):
        settings = QSettings("Diego Beltran Calderon", "EvapoGIS")
        pos = settings.value("pos", QVariant(QPoint(200, 200))).toPoint()
        size = settings.value("size", QVariant(QSize(400, 400))).toSize()
        screen = QDesktopWidget().screenGeometry()
        #size = self.geometry()
        self.resize(screen.width(),screen.height())
        self.move(0, 0)
    
    def fileOpen(self):
        self.cvsfileName = QFileDialog.getOpenFileName(self,
                                         self.tr("Importar cvs a DataBase"),"/",
                                         self.tr("All Files (*);;Text Files (*.txt)"))
        
        if not self.cvsfileName.isEmpty():
            #self.openFileNameLabel.setText(fileName)
            self.progressWidget.show()
            self.progressWidget.setWindowTitle("Exportando a DataBase")
            timer = QTimer(self)
            self.connect(timer, SIGNAL("timeout()"), self.advanceProgressBar)
            timer.start(100)
            try:
                t = MiThread()
                t.start()
            except:
                print "Error 1"

            
    
    def runSQLquery(self):
        self.sqlquery = QWidget()
        self.editquery = QLineEdit(self.tablaDb)
        titel = "<font color=gray size=3>Search</font>"
        self.labelquery = QLabel(titel    )
        self.labelquery2 = QLabel("<font color=gray size=3>Registros:</font>")
        self.resultquery = QTableWidget()
        self.resultquery.clear()
        layoutQuery = QVBoxLayout()
        layoutQuery.addWidget(self.labelquery)
        layoutQuery.addWidget(self.editquery)
        layoutQuery.addWidget(self.labelquery2)
        layoutQuery.addWidget(self.resultquery)        
        self.sqlquery.setLayout(layoutQuery)
        self.sqlquery.move(0,0)
        self.sqlquery.resize(self.mdi.size())
        self.sqlquery.setWindowTitle("Consulta a la base de datos")
        self.mdi.addWindow(self.sqlquery)
        self.sqlquery.show()
        self.connect(self.editquery, SIGNAL("returnPressed ()"),self.updatesqlQuery)
        
    def updatesqlQuery(self):
        db = dbapi.connect(str(self.dbfileName))
        cursor = db.cursor()
        param = self.editquery.text()
        query = "SELECT * FROM %s"%param
        query = cursor.execute(query)
        query = query.fetchall()
        self.resultquery.setColumnCount(14)
        self.resultquery.setRowCount(int(len(query)))
        #self.resultquery.setText(query)
        headerH = ['Estation', 'Year', "Enero", "Febrero", "Marzo", "Abril", "Mayo",
                   "Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"]
        self.resultquery.setHorizontalHeaderLabels(headerH)
        for i in range(len(query)):
            for j in range(len(query[i])):
                query_i = query[i]
                text="%s"%query_i[j].replace('"',' ').replace(',',' ')
                item = QTableWidgetItem(text)
                item.setTextAlignment(Qt.AlignRight|Qt.AlignVCenter)
                self.resultquery.setItem(i, j, item)
        self.resultquery.resizeColumnsToContents()


    def advanceProgressBar(self):
        curVal = self.progressBar.value()
        maxVal = self.progressBar.maximum()
        self.progressBar.setValue(curVal + (maxVal + curVal) / 5)

    
    def importcvsOpen(self):
        self.fileName = QFileDialog.getOpenFileName(self,
                                         self.tr("Exportar xls a cvs"),"/",
                                         self.tr("All Files (*);;Text Files (*.txt)"))
        
        if not self.fileName.isEmpty():
            try:
                h = xls2cvsThread()
                h.start()
            except:
                print "error 1"
            
       
    def ataque(self):
        errorBox = QMessageBox.about(self, self.tr("About EvapoGIS"),self.tr(
              "<p>Esta aplicacion es realizada utilizando herramientas de <b>software libre</b>"
              " y distribuida bajo una licencia GNU-GPL, para proteger la libertad del usuario"
              " y los derechos de autor. </p><p>Autor: Diego Beltran  es estudiante de"
              " Ing. Agricola de la Universidad Nacional de Colombia.\n  "
              " <b>dbcaturra@gmail.com</b></p>"))
        self.showMessage()
                                #self.tr("<h3 color='blue'></h3><p>Licencia: GPL</p> "))
    #Evapotranspiracion
    
    def penmanMotein(self):
        db = dbapi.connect(str(self.dbfileName))
        cursor = db.cursor()
        query = "SELECT * FROM %s"%"RADIACIONSOLAR"
        query = cursor.execute(query)
        query = query.fetchall()
        print query[0]
        
    def sqlQueries(self):
        #SQL Queries
        self.sqlWidget = QWidget()
        sql = QLabel("SQL: ")
        self.sql = QLineEdit("TABLA,CAMPO,VALOR")        
        sqlLayout = QHBoxLayout()
        sqlLayout.addWidget(sql)
        sqlLayout.addWidget(self.sql)
        self.sqlWidget.setLayout(sqlLayout)
        self.connect(self.sql,
                SIGNAL("returnPressed()"), self.queries)

#************************************************************************
#************************************************************************
#************************************************************************
    def queries(self):
        #Buequeda personalizada SQL
        db = dbapi.connect(str(self.dbfileName))
        cursor = db.cursor()
        self.sql = str(self.sql.text())
        self.sql = self.sql.split(",")
        query = "SELECT * FROM %s WHERE %s=%s "%(self.sql[0],self.sql[1],self.sql[2])
        query = cursor.execute(query)
        query = query.fetchall()
        self.tablaquery = QTableWidget()
        self.tablaquery.clear()
        self.tablaquery.setColumnCount(14)
        self.tablaquery.setRowCount(int(len(query)))
        #self.resultquery.setText(query)
        headerH = ['Estation', 'Year', "Enero", "Febrero", "Marzo", "Abril", "Mayo",
                   "Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"]
        self.tablaquery.setHorizontalHeaderLabels(headerH)
        for i in range(len(query)):
            for j in range(len(query[i])):
                query_i = query[i]
                text="%s"%query_i[j].replace('"',' ').replace(',',' ')
                item = QTableWidgetItem(text)
                item.setTextAlignment(Qt.AlignRight|Qt.AlignVCenter)
                self.tablaquery.setItem(i, j, item)
        self.tablaquery.resizeColumnsToContents()
        self.mdi.addWindow(self.tablaquery)
        self.tablaquery.show()
#************************************************************************
#************************************************************************
#************************************************************************        
        
        


#Splash
class SplashItem(QGraphicsWidget):
    def __init__(self, parent=None):
        QGraphicsWidget.__init__(self, parent)
        self.opacity = 0.5
        self.timeLine = QTimeLine(350)
        self.timeLine.setCurveShape(QTimeLine.EaseInCurve)
        self.connect(self.timeLine, SIGNAL("valueChanged(qreal)"), self.setValue)
        self.text = self.tr("Bienvenido a keyJoder."\
                " Presione cualquier tecla para continuar.")
        self.resize(500, 175)

    def paint(self, painter, qstyleoptiongraphicsitem, qwidget):
        painter.setOpacity(self.opacity)
        painter.setPen(QPen(Qt.black, 2))
        painter.setBrush(QColor(245, 245, 255, 220))
        painter.setClipRect(self.rect())
        painter.drawRoundRect(3, -100 + 3, 450 - 6, 250 - 6)

        textRect = self.rect().adjusted(20, 10, -10, -10)


        font = QFont()
        font.setPixelSize(35)
        painter.setPen(Qt.black)
        painter.setFont(font)
        painter.drawText(textRect, self.text)

    def keyPressEvent(self, event):
        if self.timeLine.state() == QTimeLine.NotRunning:
            self.timeLine.start()

    def setValue(self, value):
        self.opacity = 1 - value
        self.setPos(self.x(), self.scene().sceneRect().top() - self.rect().height() * value)
        if value == 1:
            self.hide()

class MiThread(threading.Thread):
    def run(self):
        self.cvsfileName = mainWin.cvsfileName
        self.dbfileName = mainWin.dbfileName
        llenarDB(self.cvsfileName,self.dbfileName)
        mainWin.progressWidget.hide()

class xls2cvsThread(threading.Thread):
    def run(self):
        self.fileName = mainWin.fileName
        self.fileName = self.fileName
        importCVS(self.fileName)


            
            


        

if __name__ == "__main__":
    app = QApplication(sys.argv)
    if not QSystemTrayIcon.isSystemTrayAvailable():
        QMessageBox.critical(None, QObject.tr(app, "Systray"),
                QObject.tr(app, "I couldn't detect any system tray on "
                    "this system."))
    #app.setPalette (QPalette.Shadow)
    splash = QSplashScreen()
    splash.setPixmap(QPixmap(":/images/OpenAgro.png"))
    splash.show()
    mainWin = MainWindow()
    topRight = Qt.AlignRight | Qt.AlignTop
    splash.showMessage(QObject.tr(mainWin,"Configurando keyjoder..."),
                    topRight, Qt.white)
    mainWin.readSettings()
    splash.showMessage(QObject.tr(mainWin,"Cargando modulos..."),
                    topRight, Qt.white);
    mainWin.createActions()
    mainWin.createTrayIcon()
    mainWin.trayIcon.show()
    #icon = QSystemTrayIcon.MessageIcon(QSystemTrayIcon.Information)
    #mainWin.trayIcon.showMessage("Bienvenido a EvapoGIS",
    #            "Estamos seguros de que usted ... dbcaturra@gmail.com", icon,
    #            40*1000)
    mainWin.createMenus()
    splash.showMessage(QObject.tr(mainWin,"Estableciendo conexiones..."),
                    topRight, Qt.white)
    mainWin.createToolBars()
    mainWin.createStatusBar()
    mainWin.setWindowIcon(QIcon(":/images/tux.png"))
    mainWin.show()
    splash.finish(mainWin)
    sys.exit(app.exec_())        
        
